package com.person.jc.service.service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.person.jc.common.SQLSpell;
import com.person.jc.service.Service;
import com.person.jc.utils.Utils;

public class BsBillingService extends Service{
	public final static BsBillingService me = new BsBillingService();
	public List<Record> getList(Map<String,Object> map){
		String temp = "";
		List<Object> params = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT t.order_id,t.order_no,t.order_status,u1.user_name service_user,c.cust_name,u2.user_name opt_user,t.order_type,t.task_date, ");
		sql.append("b.total_price,b.status,b.ticket_way,b.order_code,b.ticket_user ");
		sql.append("FROM bs_order t ");
		sql.append("LEFT JOIN bd_customer c ON t.cust_id = c.cust_id ");
		sql.append("LEFT JOIN ts_user u1 ON t.service_user = u1.user_id ");
		sql.append("LEFT JOIN ts_user u2 ON t.opt_user = u2.user_id ");
		sql.append("LEFT JOIN bs_billing b ON t.order_id = b.order_id where t.order_type in (1,2) ");
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("custId")))){
			sql.append(" and t.cust_id = ? ");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("status")))){
			sql.append(" and b.status = ? ");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("startDate")))){
			sql.append(" and DATE_FORMAT(t.opt_date,'%Y-%m-%d') >= ?");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("endDate")))){
			sql.append(" and DATE_FORMAT(t.opt_date,'%Y-%m-%d') <= ?");
			params.add(temp);
		}
		sql.append(" order by t.opt_date desc");
		return Db.find(sql.toString(), Utils.getObjParam(params));
	}
	
	
	public Record getOrderInfo(String orderId){
		String sql = "select t.*,t.service_user serviceUser,("+SQLSpell.customerName("t.cust_id")+") customerName,("+SQLSpell.userName("t.service_user")+") serviceUserName from bs_order t where order_id = ? ";
		return Db.findFirst(sql, orderId);
	}

	// 鑾峰彇寮�绁ㄤ俊鎭�
	public Record getBillingInfo(String orderId) {
		String sql = "select b.*,b.billing_unit cust_id,b.salesman serviceUser,("+SQLSpell.customerName("b.billing_unit")+") customerName,("+SQLSpell.userName("b.billing_user")+") billingUserName,("+SQLSpell.userName("b.salesman")+") serviceUserName from bs_billing b where order_id = ? ";
		return Db.findFirst(sql, orderId);
	}

	public List<Record> getOrderDetail(String orderId){
		String sql = "select boe.device_type ,COUNT(boe.device_type) num,bdi.device_name device_model from bs_order_equipment boe\r\n" + 
				"LEFT JOIN bd_device_info bdi ON bdi.device_type = boe.device_type\r\n" + 
				"where boe.order_id = ? and boe.device_type in (1,2) group by boe.device_type";
		List<Record> list = Db.find(sql, orderId);
		String gasSql = "select gas_type from bs_order_equipment where order_id =? and device_type=3";
		List<Record> gasList = Db.find(gasSql, orderId);
		
		if(gasList!=null&&gasList.size()>0) {
			String gasType = "";
			for(Record record : gasList) {
				gasType += record.get("gas_type")+",";
			}
			// 将字符串转为数组
			String[] gas = gasType.split(",");
			HashMap<String, Integer> map = new HashMap<>();
			for(int i=0;i<gas.length;i++) {
				if(gas[i].equals(""))continue;
				if(map.get(gas[i])==null) {
					map.put(gas[i], 1);
				}else {
					map.put(gas[i],map.get(gas[i])+1);
				}
			}
			Set<String> keys = map.keySet();
			for(Iterator<String> iter = keys.iterator();iter.hasNext();) {
				String key =  iter.next();
				Record record = new Record();
				record.set("device_model", key.equals("1")?"CO气检仪":key.equals("2")?"Ex气检仪":key.equals("3")?"H2S气检仪":key.equals("4")
						?"O2气检仪":key.equals("5")?"SO2气检仪":key.equals("6")?"氨气气检仪":key.equals("7")?"氯气气检仪":"");
				record.set("num", map.get(key));
				list.add(record);
				 System.out.println(key + "=" + map.get(key));
			}
		}
		return list;
	}


	public List<Record> getBillingDetail(String orderId) {
		String sql = "select d.*,d.device_model deviceModel from bs_billing_detail d LEFT JOIN bs_billing bb ON bb.billing_id = d.billing_id WHERE bb.order_id = ? ";
		return Db.find(sql, orderId);
	}


	public void save(Map<String, Object> map) {
		List<Object> params = new ArrayList<Object>();
		String billingId = UUID.randomUUID().toString();
		billingId = billingId.replaceAll("-", "");
		String sql = "insert into bs_billing (billing_id,order_id,total_price,billing_name,billing_date,"
				+ "status,billing_user,billing_unit,salesman) value(?,?,?,?,?,?,?,?,?)";
		params.add(billingId);
		params.add(map.get("orderId"));
		params.add(map.get("totalPrice"));
		params.add(map.get("billingName"));
		params.add(map.get("billingDate"));
		params.add(1);
		params.add(map.get("optUser"));
		params.add(map.get("custId"));
		params.add(map.get("serviceUser"));
		Db.update(sql, Utils.getObjParam(params));
		
		String detailSql = "insert into bs_billing_detail (billing_detail_id,billing_id,device_model,num,unit_price,total_price)"
				+ " value(?,?,?,?,?,?)";
		// 如果是数组
		if(map.get("unitPrice") instanceof String[]) {
			String[] unitPrice = (String[]) map.get("unitPrice");
			for(int i=0;i<unitPrice.length;i++) {
				String billingDetailId = UUID.randomUUID().toString();
				billingDetailId = billingDetailId.replaceAll("-", "");
				List<Object> detailParams = new ArrayList<Object>();
				detailParams.add(billingDetailId);
				detailParams.add(billingId);
				detailParams.add(((String[])map.get("deviceModel"))[i]);
				detailParams.add(Integer.parseInt(((String[])map.get("num"))[i]));
				detailParams.add(Double.parseDouble(((String[])map.get("unitPrice"))[i]));
				detailParams.add(Integer.parseInt(((String[])map.get("num"))[i])*Double.parseDouble(((String[])map.get("unitPrice"))[i]));
				Db.update(detailSql, Utils.getObjParam(detailParams));
			}
		}else {
			String billingDetailId = UUID.randomUUID().toString();
			billingDetailId = billingDetailId.replaceAll("-", "");
			List<Object> detailParams = new ArrayList<Object>();
			detailParams.add(billingDetailId);
			detailParams.add(billingId);
			detailParams.add(map.get("deviceModel"));
			detailParams.add(Integer.parseInt((String) map.get("num")));
			detailParams.add(Double.parseDouble((String)map.get("unitPrice")));
			detailParams.add(Integer.parseInt((String)map.get("num"))*Double.parseDouble((String)map.get("unitPrice")));
			Db.update(detailSql, Utils.getObjParam(detailParams));
		}
	}


	public void update(Map<String, Object> map) {
		List<Object> params = new ArrayList<Object>();
		String sql = "update bs_billing set order_id= ?,total_price= ?,billing_name= ?,billing_date= ?,"
				+ "status= ?,billing_user= ?,billing_unit= ? where billing_id= ?";
		params.add(map.get("orderId"));
		params.add(map.get("totalPrice"));
		params.add(map.get("billingName"));
		params.add(map.get("billingDate"));
		params.add(1);
		params.add(map.get("billingUser"));
		params.add(map.get("custId"));
		params.add(map.get("billingId"));
		Db.update(sql, Utils.getObjParam(params));
		
		String detailSql = "update bs_billing_detail set billing_id= ?,device_model= ?,num=?,unit_price=?,total_price=? where billing_detail_id= ?";
		// 如果是数组
		if(map.get("unitPrice") instanceof String[]) {
			String[] unitPrice = (String[]) map.get("unitPrice");
			for(int i=0;i<unitPrice.length;i++) {
				List<Object> detailParams = new ArrayList<Object>();
				detailParams.add(map.get("billingId"));
				detailParams.add(((String[])map.get("deviceModel"))[i]);
				detailParams.add(Integer.parseInt(((String[])map.get("num"))[i]));
				detailParams.add(Double.parseDouble(((String[])map.get("unitPrice"))[i]));
				detailParams.add(Integer.parseInt(((String[])map.get("num"))[i])*Double.parseDouble(((String[])map.get("unitPrice"))[i]));
				detailParams.add(((String[])map.get("billingDetailId"))[i]);
				Db.update(detailSql, Utils.getObjParam(detailParams));
			}
		}else {
			List<Object> detailParams = new ArrayList<Object>();
			detailParams.add(map.get("billingId"));
			detailParams.add(map.get("deviceModel"));
			detailParams.add(Integer.parseInt((String) map.get("num")));
			detailParams.add(Double.parseDouble((String)map.get("unitPrice")));
			detailParams.add(Integer.parseInt((String)map.get("num"))*Double.parseDouble((String)map.get("unitPrice")));
			detailParams.add(map.get("billingDetailId"));
			Db.update(detailSql, Utils.getObjParam(detailParams));
		}
		
	}

	// 获取证书详情
	public List<Record> getDetailList(String orderId) {
		String sql = "select *,id certificate_id from bs_certificate  where order_id=?";
		return Db.find(sql,orderId);
	}
	
	
}
